/* execute a list of .sql files against the server and DB specified  */  

-- Ref: http://stackoverflow.com/questions/241925/is-it-possible-to-execute-a-text-file-from-sql-query

USE [master];
GO

SET NOCOUNT ON

SET XACT_ABORT ON
BEGIN TRAN

	DECLARE @DBServerName   VARCHAR(100) = 'servername'
	DECLARE @DBName VARCHAR(100) = 'db name'
	DECLARE @FilePath   VARCHAR(200) = 'path to scrips\'

	/*	create a holder for all filenames to be executed  */

	DECLARE @FileList TABLE (Files NVARCHAR(MAX))

	INSERT INTO @FileList VALUES ('script 1.sql')
	INSERT INTO @FileList VALUES ('script 2.sql')
	INSERT INTO @FileList VALUES ('script X.sql')

	WHILE (SELECT COUNT(Files) FROM @FileList) > 0
	BEGIN
	   /*  execute each file one at a time  */
	   DECLARE @FileName NVARCHAR(MAX) = (SELECT TOP(1) Files FROM @FileList)
	   DECLARE @command  VARCHAR(500)  = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i "' + @FilePath + @Filename +'"'
	   EXEC xp_cmdshell  @command

	   PRINT 'EXECUTED: ' + @FileName
	   DELETE FROM @FileList WHERE Files = @FileName
	END

COMMIT TRAN